-- **************************************************************************
--    Project Name:   新增小件上件表-清洗
--    Job Name:       jms_dwd.dwd_tab_ass_scan_small_upper_base_hi
--    Description :   新增小件上件表-清洗
--    Author :        荣光凡
--    date：          2022/06/08
-- **************************************************************************
--
-- **************************************************************************
--    modify by 荣光凡 2022/06/08 新增脚本
-- **************************************************************************
--    Relation:
--    jms_dwd.dwd_tab_ass_scan_small_upper_base_hi << [
--         jms_ods.ass_scan_small_upper_hi
--    ]
-- **************************************************************************

insert overwrite table jms_dwd.dwd_tab_ass_scan_small_upper_base_hi partition(dt)
select
    waybill_no
    ,network_id
    ,network_code
    ,network_name
    ,scan_time
    ,user_num
    ,weight
    ,length
    ,wide
    ,high
    ,upload_result
    ,cross_belt_mac
    ,supply_desk_code
    ,supply_desk_mac
    ,upload_time
    ,enter_time
    ,sorting_plan_code
    ,operate_type
    ,equipment_code
    ,equipment_layer
    ,owned_venue_type
    ,agency_area_name
    ,agency_area_code
    ,franchisee_name
    ,franchisee_code
    ,grid_no
    ,package_no
    ,fall_time
    ,next_station
    ,cycles_num
    ,car_num
    ,grid_code
    ,third_code
    ,BAG_USER_NAME
    ,bag_user_code
    ,dest_province_id
    ,dest_city_name
    ,network_code_by_code
    ,network_name_by_code
    ,DEST_PROVINCE_NAME
    ,DEST_CITY_ID
    ,dt
from (
    select
        waybill_no
        ,network_id
        ,network_code
        ,network_name
        ,scan_time
        ,user_num
        ,weight
        ,length
        ,wide
        ,high
        ,upload_result
        ,cross_belt_mac
        ,supply_desk_code
        ,supply_desk_mac
        ,upload_time
        ,enter_time
        ,sorting_plan_code
        ,operate_type
        ,equipment_code
        ,equipment_layer
        ,owned_venue_type
        ,agency_area_name
        ,agency_area_code
        ,franchisee_name
        ,franchisee_code
        ,grid_no
        ,package_no
        ,fall_time
        ,next_station
        ,cycles_num
        ,car_num
        ,grid_code
        ,third_code
        ,row_number() over(partition by waybill_no,network_code,scan_time,equipment_code,supply_desk_code order by enter_time) as row_id
        ,BAG_USER_NAME
        ,bag_user_code
        ,dest_province_id
        ,dest_city_name
        ,network_code_by_code
        ,network_name_by_code
        ,DEST_PROVINCE_NAME
        ,DEST_CITY_ID
        ,dt
    from jms_ods.ass_scan_small_upper_hi
    where dt >= '{{ execution_date | hour_add(-1) | cst_hour }}'
    and dt <= '{{ execution_date | cst_hour }}'
) t_row
where row_id = 1
distribute by dt;